package cn.krguang;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

class DbOperate {

    private static String url = "jdbc:mysql://krguang.cn:3306/remote_monitor";
    private static String user = "root";
    private static String password = "MySQL17612168015";


    private DbOperate(){
    }
    private final static DbOperate instance = new DbOperate();
    public static DbOperate getInstance(){
        return instance;
    }

    void creatList() {
        Calendar calendar = Calendar.getInstance();
        String listName = "unit_data"+"_"+calendar.get(Calendar.YEAR)+"_"+(calendar.get(Calendar.MONTH)+1)+"_"+calendar.get(Calendar.DATE);
        ResultSet resultSet = null;
        Statement stmt = null;
        Connection con = null;
        String createBrandDatabase = "create table "
                + listName
                +
                "(" +
                "`id` INT(11) NOT NULL AUTO_INCREMENT," +
                "`time` VARCHAR(10)," +
                "`imei` VARCHAR(20)," +
                "`unitName` VARCHAR(10)," +
                "`tempReal` VARCHAR(10)," +
                "`humiReal` VARCHAR(10)," +
                "`tempSet` VARCHAR(10)," +
                "`humiSet` VARCHAR(10)," +
                "`yaCha1` VARCHAR(10)," +
                "`lengShuiFaKaiDu` VARCHAR(10)," +
                "`reShuiFaKaiDu` VARCHAR(10)," +
                "`jiaShiQIKaiDu` VARCHAR(10)," +
                "`dongXiaJi` VARCHAR(5)," +
                "`shouZiDong` VARCHAR(5)," +
                "`fengJiYiQiDong` VARCHAR(5)," +
                "`zhiBanStatus` VARCHAR(5)," +
                "`dianJiaRe1` VARCHAR(5)," +
                "`dianJiaRe2` VARCHAR(5)," +
                "`dianJiaRe3` VARCHAR(5)," +
                "`fengJiStatus` VARCHAR(5)," +
                "`zhongXiaoBaoJing` VARCHAR(5)," +
                "`dianJiaReGaoWen` VARCHAR(5)," +
                "`fengJiQueFeng` VARCHAR(5)," +
                "`paiFengJiYiQiDong` VARCHAR(5)," +
                "`diWenPanGuan` VARCHAR(5)," +
                "`mieJunYunXing` VARCHAR(5)," +
                "`xinFengWenDu` VARCHAR(10)," +
                "`dianYuRe1` VARCHAR(5)," +
                "`dianYuRe2` VARCHAR(5)," +
                "`dianYuRe3` VARCHAR(5)," +
                "`dianYuReGaoWen` VARCHAR(5)," +

                "PRIMARY KEY (`id`)" +
                ")"
                + " DEFAULT CHARSET=utf8";
        String checkTable="show tables like "+"\""+listName+"\"";

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection(url,
                    user, password);
            stmt = (Statement) con.createStatement();

            resultSet=stmt.executeQuery(checkTable);
            if (resultSet.next()) {
                System.out.println("table exist!");
            }else{
                if(stmt.executeUpdate(createBrandDatabase)==0)
                    System.out.println("create table success!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    void insertUnitData(UnitData unitData ){

        Connection con = null;
        Statement stmt = null;
        Calendar calendar = Calendar.getInstance();
        String listName = "unit_data"+"_"+calendar.get(Calendar.YEAR)+"_"+(calendar.get(Calendar.MONTH)+1)+"_"+calendar.get(Calendar.DATE);
        String time = calendar.get(Calendar.HOUR_OF_DAY)+"_"+calendar.get(Calendar.MINUTE)+"_"+calendar.get(Calendar.SECOND);
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection(url,
                    user, password);
            stmt = (Statement) con.createStatement();
            String insertData = "INSERT INTO " +listName+
                    " (" +
                    "`time`,"+
                    "`imei`," +
                    "`unitName`," +
                    "`tempReal`," +
                    "`humiReal`," +
                    "`tempSet`," +
                    "`humiSet`," +
                    "`yaCha1`," +
                    "`lengShuiFaKaiDu`," +
                    "`reShuiFaKaiDu`," +
                    "`jiaShiQIKaiDu`," +
                    "`dongXiaJi`," +
                    "`shouZiDong`," +
                    "`fengJiYiQiDong`," +
                    "`zhiBanStatus`," +
                    "`dianJiaRe1`," +
                    "`dianJiaRe2`," +
                    "`dianJiaRe3`," +
                    "`fengJiStatus`," +
                    "`zhongXiaoBaoJing`," +
                    "`dianJiaReGaoWen`," +
                    "`fengJiQueFeng`," +
                    "`paiFengJiYiQiDong`," +
                    "`diWenPanGuan`," +
                    "`mieJunYunXing`," +
                    "`xinFengWenDu`," +
                    "`dianYuRe1`," +
                    "`dianYuRe2`," +
                    "`dianYuRe3`," +
                    "`dianYuReGaoWen`" +

                    ") " +
                    "VALUES"+
                    " (" +
                    "\""+time+"\""+","+
                    "\""+unitData.getImei()+"\""+","+
                    "\""+unitData.getUnitName()+"\""+","+
                    unitData.getTempReal()+","+
                    unitData.getHumiReal()+","+
                    unitData.getTempSet()+","+
                    unitData.getHumiSet()+","+
                    unitData.getYaCha1()+","+
                    unitData.getLengShuiFaKaiDu()+","+
                    unitData.getReShuiFaKaiDu()+","+
                    unitData.getJiaShiQIKaiDu()+","+
                    unitData.getDongXiaJi()+","+
                    unitData.getShouZiDong()+","+
                    unitData.getFengJiYiQiDong()+","+
                    unitData.getZhiBanStatus()+","+
                    unitData.getDianJiaRe1()+","+
                    unitData.getDianJiaRe2()+","+
                    unitData.getDianJiaRe3()+","+
                    unitData.getFengJiStatus()+","+
                    unitData.getZhongXiaoBaoJing()+","+
                    unitData.getDianJiaReGaoWen()+","+
                    unitData.getFengJiQueFeng()+","+
                    unitData.getPaiFengJiYiQiDong()+","+
                    unitData.getDiWenPanGuan()+","+
                    unitData.getMieJunYunXing()+","+
                    unitData.getXinFengWenDu()+","+
                    unitData.getDianYuRe1()+","+
                    unitData.getDianYuRe2()+","+
                    unitData.getDianYuRe3()+","+
                    unitData.getDianYuReGaoWen()+
                    ");";
            try {
             //   System.out.println("SQL = "+insertData);
                stmt.executeUpdate(insertData);

            }catch (MySQLSyntaxErrorException e){
                e.printStackTrace();
                creatList();
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {

            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    void insertWarningData(WarningData warningData){
        Connection con = null;
        Statement stmt = null;
        String time = getCurrentTime();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection(url,
                    user, password);
            stmt = (Statement) con.createStatement();
            String insertData = "INSERT INTO " +"unit_warning"+
                    " (" +
                    "`warning_time`,"+
                    "`warning_data`," +
                    "`unit_name`," +
                    "`warning_name`," +
                    "`imei`" +
                    ") " +
                    "VALUES"+
                    " (" +
                    "\""+time+"\""+","+
                    "\""+warningData.getWarningData()+"\""+","+
                    "\""+warningData.getUnitName()+"\""+","+
                    "\""+warningData.getWarningName()+"\""+","+
                    "\""+warningData.getImei()+"\""+
                    ");";
            try {
                System.out.println(insertData);
                stmt.executeUpdate(insertData);
            }catch (MySQLSyntaxErrorException e){
                e.printStackTrace();
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {

            if (stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    private String getCurrentTime(){

        Date date = new Date();
        DateFormat df=new SimpleDateFormat("yyyyMMddHHmmss");
        return df.format(date);
    }
}
